#region - Class details -
/*  
 *  Developer   : Wicus Botha
 *  Description : 
 *  History     : WB - 2007-12-24 - Created
 */
#endregion

using System;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;

namespace SqlServerAdmin
{
	/// <summary>
	/// Description of SqlServerDecrypt.
	/// </summary>
	public class SqlDecryptObject
	{
		//constructor
		public SqlDecryptObject(SqlConnection DBconnection)			
		{
            connectionDB = DBconnection;
            CMD = DBconnection.CreateCommand();
		}
		
		#region - const SQL -
		private const string objName = "#name#";
		private const string objType = "#type#";
		private const string objLength = "#lentgh#";
		private const string sqlWithEncrypt = "with encryption";
		private const string sqlErrorCheck = "DECLARE @Error VARCHAR(100) DECLARE @ObjectID INT " +
											"SET @ObjectID = OBJECT_ID('" + objName + "') SET @Error = '' " +
											"IF @ObjectID IS NULL " +
											"	SET @Error = 'The object name does not exist in the database.' " +
											"IF (@Error = '') AND " +
											"   (NOT EXISTS(SELECT TOP 1 * FROM syscomments WHERE id = @ObjectID AND encrypted = 1)) " +
											"	SET @Error = 'The object provided exists however it is not encrypted. Aborting.' " +
											"SELECT @Error as Error ";
		private const string sqlObjectType = "DECLARE @ObjectType NVARCHAR(128) " +
											"DECLARE @ObjectName NVARCHAR(128) " +
											"SET @ObjectName = '" + objName + "' " +
											"IF OBJECT_ID(@ObjectName, 'PROCEDURE') IS NOT NULL " +
											"	SET @ObjectType = 'PROCEDURE' " +
											"ELSE " +
											"	IF OBJECT_ID(@ObjectName , 'TRIGGER') IS NOT NULL " +
											"		SET @ObjectType = 'TRIGGER' " +
											"	ELSE " +
											"		IF OBJECT_ID(@ObjectName, 'VIEW') IS NOT NULL " +
											"			SET @ObjectType = 'VIEW' " +
											"		ELSE " +
											"			SET @ObjectType = 'FUNCTION' " +
											"SELECT @ObjectType as ObjectType ";
		private const string sqlObjectDataLength = "SELECT SUM(DATALENGTH(ctext)/2) as ObjectLength "+
											" FROM syscomments WHERE id = OBJECT_ID('" + objName + "')";
		private const string sqlObjectLengthLess4000 =
											"DECLARE @ObjectName NVARCHAR(128) " +
											"DECLARE @ObjectType NVARCHAR(128) " +
											"DECLARE @ObjectDataLength INT " +
											"SET @ObjectName =  '" + objName + "' " +
											"SET @ObjectType = '" + objType + "' " +
											"SET @ObjectDataLength =  " + objLength + "" +
											"DECLARE @i INT " +
											"DECLARE @ContentOfEncryptedObject NVARCHAR(4000) " +
											"DECLARE @ContentOfDecryptedObject NVARCHAR(4000) " +
											"DECLARE @ContentOfFakeObject NVARCHAR(4000) " +
											"DECLARE @ContentOfFakeEncryptedObject NVARCHAR(4000) " +
											"SELECT TOP 1 @ContentOfEncryptedObject = ctext FROM syscomments WHERE id = OBJECT_ID(@ObjectName) " +
											"SET @ContentOfFakeObject = N'ALTER ' + @ObjectType + N' ['+ @ObjectName + N'] WITH ENCRYPTION AS' " +
											"SET @ContentOfFakeObject = @ContentOfFakeObject + REPLICATE(N'-', @ObjectDataLength - (DATALENGTH(@ContentOfFakeObject)/2)) " +
											"BEGIN TRAN " +
											"	EXEC(@ContentOfFakeObject) " +
											"	SELECT TOP 1 @ContentOfFakeEncryptedObject = ctext FROM syscomments WHERE id = OBJECT_ID(@ObjectName) " +
											"IF @@TRANCOUNT > 0 " +
											"	ROLLBACK TRAN " +
											"SET @ContentOfFakeObject = N'CREATE ' + @ObjectType + N' [' + @ObjectName + N'] WITH ENCRYPTION AS' " +
											"SET @ContentOfFakeObject = @ContentOfFakeObject + REPLICATE(N'-', @ObjectDataLength - (DATALENGTH(@ContentOfFakeObject)/2)) " +
											"SET @i = 1 " +
											"SET @ContentOfDecryptedObject = N'' " +
											"SET @ContentOfDecryptedObject = @ContentOfDecryptedObject + REPLICATE(N'A', @ObjectDataLength - (DATALENGTH(@ContentOfDecryptedObject)/2))" +
											"WHILE @i <= @ObjectDataLength " +
											"BEGIN " +
											"	SET @ContentOfDecryptedObject = STUFF(@ContentOfDecryptedObject, @i, 1, " +
											"							NCHAR(  UNICODE(SUBSTRING(@ContentOfEncryptedObject, @i, 1)) ^ " +
											"							(       UNICODE(SUBSTRING(@ContentOfFakeObject, @i, 1)) ^ " +
											"									UNICODE(SUBSTRING(@ContentOfFakeEncryptedObject, @i, 1)) " +
											"							))) " +
											"	SET @i = @i + 1 " +
											"END " +
											"SELECT 0,@ContentOfDecryptedObject ";		
		private const string sqlObjectLengthMore4000 =
											"DECLARE @ObjectName NVARCHAR(128) " +
											"DECLARE @ObjectType NVARCHAR(128) " +
											"DECLARE @ObjectDataLength INT " +
											"SET @ObjectName =  '" + objName + "' " +
											"SET @ObjectType = '" + objType + "' " +
											"SET @ObjectDataLength =  " + objLength + "" +
											"DECLARE @i INT " +
											"DECLARE @j INT " +
											"DECLARE @k INT " +
											"DECLARE @ContentOfEncryptedObject NVARCHAR(4000) " +
											"DECLARE @ContentOfDecryptedObject NVARCHAR(4000) " +
											"DECLARE @ContentOfFakeObject NVARCHAR(4000) " +
											"DECLARE @ContentOfFakeEncryptedObject NVARCHAR(4000) " +
											"DECLARE @CurrColID INT " +
											"DECLARE @CurrDataLength INT " +
											"DECLARE @CurrPtr BINARY(16) " +
											"DECLARE @CurrOffset INT " +
											"DECLARE @tmpStrForExec VARCHAR(8000) " +
											"IF OBJECT_ID('tempdb..#tblTemp') IS NOT NULL " +
											"	DROP TABLE #tblTemp " +
											"CREATE TABLE #tblTemp ( " +
											"	Idx INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED, " +
											"    ContentOfEncryptedObject NTEXT, " +
											"    ContentOfDecryptedObject NTEXT, " +
											"	ContentOfFakeObject NTEXT, " +
											"	ContentOfFakeEncryptedObject NTEXT) " +
											"INSERT INTO #tblTemp (ContentOfEncryptedObject) " +
											"VALUES(N'') " +
											"SELECT @CurrColID = MIN(colid) FROM syscomments WHERE id = OBJECT_ID(@ObjectName) " +
											"WHILE @CurrColID IS NOT NULL " +
											"BEGIN " +
											"	SET @ContentOfEncryptedObject = NULL " +
											"	SELECT @ContentOfEncryptedObject = ctext FROM syscomments WHERE id = OBJECT_ID(@ObjectName) AND colid = @CurrColID " +
											"	SELECT @CurrPtr = TEXTPTR(ContentOfEncryptedObject), " +
											"		   @CurrDataLength = ISNULL(DATALENGTH(ContentOfEncryptedObject), 0) " +
											"	FROM #tblTemp WHERE Idx = 1 " +
											"	IF @CurrDataLength = 0 " +
											"	BEGIN " +
											"		UPDATE #tblTemp SET ContentOfEncryptedObject = @ContentOfEncryptedObject WHERE Idx = 1 " +
											"	END " +
											"	ELSE " +
											"	BEGIN " +
											"		SET @CurrOffset = NULL " +
											"		UPDATETEXT #tblTemp.ContentOfEncryptedObject @CurrPtr @CurrOffset 0 @ContentOfEncryptedObject " +
											"	END " +
											"    SELECT @CurrColID = MIN(colid) FROM syscomments WHERE id = OBJECT_ID(@ObjectName) AND colid > @CurrColID " +
											"END                         " +
											"SET @ContentOfFakeObject = N'ALTER ' + @ObjectType + N' ['+ @ObjectName + N'] WITH ENCRYPTION AS' " +
											"UPDATE #tblTemp SET ContentOfFakeObject = @ContentOfFakeObject WHERE Idx = 1 " +
											"SELECT  @CurrPtr = TEXTPTR(ContentOfFakeObject), " +
											"		@CurrDataLength = ISNULL(DATALENGTH(ContentOfFakeObject)/2, 0) " +
											"FROM #tblTemp WHERE Idx = 1 " +
											"WHILE @CurrDataLength < @ObjectDataLength " +
											"BEGIN " +
											"	SET @CurrOffset = NULL " +
											"	IF @CurrDataLength + 4000 < @ObjectDataLength " +
											"		SET @ContentOfFakeObject = REPLICATE(N'-', 4000) " +
											"	ELSE " +
											"        SET @ContentOfFakeObject = REPLICATE(N'-', @ObjectDataLength - @CurrDataLength) " +
											"	UPDATETEXT #tblTemp.ContentOfFakeObject @CurrPtr @CurrOffset 0 @ContentOfFakeObject " +
											"	SELECT  @CurrPtr = TEXTPTR(ContentOfFakeObject), " +
											"			@CurrDataLength = ISNULL(DATALENGTH(ContentOfFakeObject)/2, 0) " +
											"	FROM #tblTemp WHERE Idx = 1 " +
											"END " +
											"SET @tmpStrForExec = '' " +
											"SELECT @j = CEILING(CAST(DATALENGTH(ContentOfFakeObject) AS DECIMAL(18, 0))/(2*2000)) FROM #tblTemp WHERE Idx = 1 " +
											"SET @i = 1 " +
											"WHILE @i <= @j " +
											"BEGIN " +
											"	SET @tmpStrForExec = @tmpStrForExec + " +
											"			'DECLARE @ContentOfDecryptedObject' + CAST(@i-1 AS VARCHAR(32)) + ' NVARCHAR(4000) ' + CHAR(10) + CHAR(13) " +
											"			+ 'SELECT @ContentOfDecryptedObject' + CAST(@i-1 AS VARCHAR(32)) + ' = SUBSTRING(ContentOfFakeObject, 1 + (' + CAST(@i-1 AS VARCHAR(32)) + '*2000), 2000) FROM #tblTemp WHERE Idx = 1' + CHAR(10) + CHAR(13) " +
											"			+ 'SET @ContentOfDecryptedObject' + CAST(@i-1 AS VARCHAR(32)) + ' = REPLACE(@ContentOfDecryptedObject' + CAST(@i-1 AS VARCHAR(32)) + ', '''''''', '''''''''''') ' + CHAR(10) + CHAR(13) " +
											"	SET @i = @i + 1 " +
											"END " +
											"SET @tmpStrForExec = @tmpStrForExec + 'EXEC(' " +
											"SET @i = 1 " +
											"WHILE @i <= @j " +
											"BEGIN " +
											"	IF @i < @j " +
											"		SET @tmpStrForExec = @tmpStrForExec + '@ContentOfDecryptedObject' + CAST(@i-1 AS VARCHAR(32)) + ' + ' " +
											"	ELSE " +
											"		SET @tmpStrForExec = @tmpStrForExec + '@ContentOfDecryptedObject' + CAST(@i-1 AS VARCHAR(32)) + ')' " +
											"	SET @i = @i + 1 " +
											"END " +
											"SET XACT_ABORT OFF " +
											"BEGIN TRAN " +
											"	EXEC(@tmpStrForExec) " +
											"IF @@ERROR <> 0 " +
											"	ROLLBACK TRAN " +
											"SELECT @CurrColID = MIN(colid) FROM syscomments WHERE id = OBJECT_ID(@ObjectName) " +
											"WHILE @CurrColID IS NOT NULL " +
											"BEGIN " +
											"	SET @ContentOfFakeEncryptedObject = NULL " +
											"	SET @CurrOffset = NULL " +
											"	SELECT @ContentOfFakeEncryptedObject = ctext FROM syscomments WHERE id = OBJECT_ID(@ObjectName) AND colid = @CurrColID " +
											"	SELECT  @CurrPtr = TEXTPTR(ContentOfFakeEncryptedObject), " +
											"			@CurrDataLength = ISNULL(DATALENGTH(ContentOfFakeEncryptedObject), 0) " +
											"	FROM #tblTemp WHERE Idx = 1 " +
											"	IF @CurrDataLength = 0 " +
											"	BEGIN " +
											"		UPDATE #tblTemp SET ContentOfFakeEncryptedObject = @ContentOfFakeEncryptedObject WHERE Idx = 1 " +
											"	END " +
											"	ELSE " +
											"	BEGIN " +
											"		SET @CurrOffset = NULL " +
											"		UPDATETEXT #tblTemp.ContentOfFakeEncryptedObject @CurrPtr @CurrOffset 0 @ContentOfFakeEncryptedObject " +
											"	END " +
											"	SELECT @CurrColID = MIN(colid) FROM syscomments WHERE id = OBJECT_ID(@ObjectName) AND colid > @CurrColID " +
											"END " +
											"SET @CurrOffset = 0 " +
											"SELECT @CurrPtr = TEXTPTR(ContentOfFakeObject) FROM #tblTemp WHERE Idx = 1 " +
											"UPDATETEXT #tblTemp.ContentOfFakeObject @CurrPtr @CurrOffset 6 'CREATE ' " +
											"SET @ContentOfDecryptedObject = REPLICATE(N'A', 4000) " +
											"SET @i = 1 " +
											"SET @j = 1 " +
											"SELECT  @ContentOfEncryptedObject = SUBSTRING(ContentOfEncryptedObject, (@j - 1)*4000 + 1, 4000), " +
											"		@ContentOfFakeObject = SUBSTRING(ContentOfFakeObject, (@j - 1)*4000 + 1, 4000), " +
											"		@ContentOfFakeEncryptedObject = SUBSTRING(ContentOfFakeEncryptedObject, (@j - 1)*4000 + 1, 4000) " +
											"FROM #tblTemp WHERE Idx = 1 " +
											"WHILE @i <= @ObjectDataLength " +
											"BEGIN " +
											"	SET @k = @i - ((@j-1)*4000) " +
											"	SET @ContentOfDecryptedObject = STUFF(@ContentOfDecryptedObject, @k, 1, " +
											"					NCHAR(  UNICODE(SUBSTRING(@ContentOfEncryptedObject, @k, 1)) ^ " +
											"					(       UNICODE(SUBSTRING(@ContentOfFakeObject, @k, 1)) ^ " +
											"							UNICODE(SUBSTRING(@ContentOfFakeEncryptedObject, @k, 1)) " +
											"					))) " +
											"	IF (@i = (@j*4000)) OR ( (@i <> (@j*4000)) AND @i = @ObjectDataLength) " +
											"	BEGIN " +
											"		SELECT  @CurrPtr = TEXTPTR(ContentOfDecryptedObject), " +
											"				@CurrDataLength = ISNULL((DATALENGTH(ContentOfDecryptedObject)/2), 0) " +
											"		FROM #tblTemp WHERE Idx = 1 " +
											"		SET @CurrOffset = NULL " +
											"		IF @CurrDataLength = 0 " +
											"			UPDATE #tblTemp SET ContentOfDecryptedObject = @ContentOfDecryptedObject WHERE Idx = 1 " +
											"		ELSE " +
											"            UPDATETEXT #tblTemp.ContentOfDecryptedObject @CurrPtr @CurrOffset 0 @ContentOfDecryptedObject " +
											"		SET @CurrDataLength = @CurrDataLength + (DATALENGTH(@ContentOfDecryptedObject)/2) " +
											"		IF @CurrDataLength + 4000 < @ObjectDataLength " +
											"			SET @ContentOfDecryptedObject = REPLICATE(N'-', 4000) " +
											"		ELSE " +
											"			SET @ContentOfDecryptedObject = REPLICATE(N'-', @ObjectDataLength - @CurrDataLength) " +
											"		SET @j = @j + 1 " +
											"		SELECT  @ContentOfEncryptedObject = SUBSTRING(ContentOfEncryptedObject, (@j - 1)*4000 + 1, 4000), " +
											"				@ContentOfFakeObject = SUBSTRING(ContentOfFakeObject, (@j - 1)*4000 + 1, 4000), " +
											"				@ContentOfFakeEncryptedObject = SUBSTRING(ContentOfFakeEncryptedObject, (@j - 1)*4000 + 1, 4000) " +
											"		FROM #tblTemp WHERE Idx = 1 " +
											"	END " +
											"	SET @i = @i + 1 " +
											"END " +
											"DECLARE @tblBypassTransaction TABLE (Idx INT IDENTITY(1, 1), ContentOfDecryptedObject NVARCHAR(4000)) " +
											"SELECT @j = CEILING(CAST(DATALENGTH(ContentOfDecryptedObject) AS DECIMAL(18, 0))/(2*4000)) " +
											"FROM #tblTemp " +
											"WHERE Idx = 1 " +
											"SET @i = 1 " +
											"WHILE @i <= @j " +
											"BEGIN " +
											"	INSERT INTO @tblBypassTransaction (ContentOfDecryptedObject) " +
											"	SELECT SUBSTRING(ContentOfDecryptedObject, 1 + (@i-1)*4000, 4000) FROM #tblTemp WHERE Idx = 1 " +
											"	SET @i = @i + 1 " +
											"END " +
											"IF @@TRANCOUNT > 0 " +
											"	ROLLBACK TRAN " +
											"SELECT * FROM @tblBypassTransaction ";
		#endregion

		#region - Private variables -
        private SqlConnection connectionDB;
		private SqlCommand CMD;
		private string pObjectType;
		private int pObjectLength;
		private string pDecryptedSql;
        private string lastError = "";
		#endregion
		
		#region - Private methods -
		private string getReplacedString(string SQL, string Param, string ReplaceValue)
		{
			int iPos = SQL.IndexOf(Param);
			if (iPos > 0)
			{
				return SQL.Substring(0,iPos) + ReplaceValue + 
					SQL.Substring(iPos + Param.Length, SQL.Length - iPos - Param.Length);
			}
			else
				return SQL;
		}
		private string get_A_QueryValue(string SQL)
		{			
			CMD.Connection = (SqlConnection)this.connectionDB;
			CMD.CommandText = SQL;
			SqlDataReader reader = CMD.ExecuteReader();
			reader.Read();
			string tmpRes = reader[0].ToString();
			reader.Close();		
			return tmpRes;
		}
		private void checkForErrors()
		{			
			string tmpSQL = getReplacedString(sqlErrorCheck,objName,this.ObjectName);
			lastError = get_A_QueryValue(tmpSQL);
		}
		private void setObjectType()
		{
			string tmpSQL = getReplacedString(sqlObjectType,objName,this.ObjectName);
			pObjectType = get_A_QueryValue(tmpSQL);
		}
		private void setObjectLength()
		{
			string tmpSQL = getReplacedString(sqlObjectDataLength,objName,this.ObjectName);
			pObjectLength = int.Parse(get_A_QueryValue(tmpSQL));
		}
		private void setDecryptedSql()
		{
			pDecryptedSql = "";
			string tmpSQL = "";
			if (pObjectLength < 4000)
				tmpSQL = sqlObjectLengthLess4000;
			else
				tmpSQL = sqlObjectLengthMore4000;
			tmpSQL = getReplacedString(tmpSQL,objName,this.ObjectName);
			tmpSQL = getReplacedString(tmpSQL,objType,pObjectType);
			tmpSQL = getReplacedString(tmpSQL,objLength,pObjectLength.ToString());
			CMD.Connection = (SqlConnection)this.connectionDB;
			CMD.CommandText = tmpSQL;
			try {
			  	SqlDataReader reader = CMD.ExecuteReader();
				while (reader.Read())
        		{
					pDecryptedSql = pDecryptedSql + reader[1].ToString();
        		}
        		reader.Close();	           
			}
			catch (Exception e){
				lastError = "Error in decryption object: " + ObjectName + ", error = " + e.Message;
			}
        	int iPos = pDecryptedSql.ToLower().IndexOf(sqlWithEncrypt);
        	if (iPos >= 0)
        	{
        		pDecryptedSql = pDecryptedSql.Remove(iPos,sqlWithEncrypt.Length);
        	}  
		}
		#endregion

		#region - Public properties / variables -
		public string ObjectName;
		public string DecryptedSql
		{
			get {return pDecryptedSql; }
		}
        public string LastError
        {
            get { return lastError; }
            set { lastError = value; }
        }
		#endregion

		#region - Public methods -
		public void DecryptSP()
		{					
			lastError = "";
			pDecryptedSql = "";
			checkForErrors();
			// Set Object type
			if (lastError.Length == 0)
			{
				setObjectType();
				setObjectLength();
				setDecryptedSql();
			}
		}
		public void ReplaceSP()
		{
			if (this.DecryptedSql != "")
			{
				CMD.Connection = (SqlConnection)this.connectionDB;
				SqlTransaction tran = (connectionDB as SqlConnection).BeginTransaction();
				CMD.Transaction = tran;
				try {
					CMD.CommandText = "DROP " + pObjectType + " " + this.ObjectName;
					CMD.ExecuteNonQuery();
					CMD.CommandText = this.DecryptedSql;
					CMD.ExecuteNonQuery();
					tran.Commit();
					lastError = "";
				} 
				catch (Exception E){
					tran.Rollback();
					lastError = "Could not create store procedure " + E.Message;
				}
				finally{
					CMD.Transaction = null;
				}					
			}
		}
		#endregion
	}
	
	public delegate void ProgressEventHandler(object source, ProgressEvent e);
	public class ProgressEvent : EventArgs
	{
		public int Progress;
		public int MaxProgress;
	}
	
	public class SqlDecryptObj
	{
		//constructor
		public SqlDecryptObj(SqlConnection ConnectionDB)			
		{
            this.connectionDB = ConnectionDB;
            sqlDecrypt = new SqlDecryptObject(ConnectionDB);
		}
		// Constants
		private const string sqlProcEncryptList = 
									"SELECT Name FROM sysobjects WHERE xtype = 'P' " +
									" AND id IN (select distinct id from syscomments where encrypted = 1) " +
						 			" ORDER BY name";

		#region - Private variables -
		private SqlDecryptObject sqlDecrypt;
		#endregion
		
		#region - Private methods -		
        private string lastError = "";
        private SqlConnection connectionDB;
		#endregion
	
		#region - Public properties / variables -		
        public string LastError
        {
            get { return lastError; }
            set { lastError = value; }
        }
		#endregion

		#region - Public methods -
		public void DecryptAll()
		{
			ProgressEvent Prog = new ProgressEvent();
			//
			SqlDataAdapter sdaDB = new SqlDataAdapter(sqlProcEncryptList, 
			                                          (SqlConnection)connectionDB);//conStr
			DataSet DataHolder = new DataSet();
			sdaDB.Fill(DataHolder);
			DataTable ObjList = new DataTable();
			ObjList = DataHolder.Tables[0];
			//
			Prog.Progress = 0;
			Prog.MaxProgress = ObjList.Rows.Count;
			for (int i = 0; i < ObjList.Rows.Count; i++)
			{
                sqlDecrypt.ObjectName = ObjList.Rows[i][0].ToString();
				try {
                    sqlDecrypt.DecryptSP();
                    sqlDecrypt.ReplaceSP();
                    if (sqlDecrypt.LastError != "")
                        lastError = lastError + "\n" + sqlDecrypt.LastError;				
					
				} catch (Exception) {
                    lastError = lastError + "\n" + " Error with object: " + sqlDecrypt.ObjectName;
				}
				Prog.Progress++;
				DoProgress(this,Prog);
        	}        	           			
		}
		public event ProgressEventHandler DoProgress;	
		
		#endregion
	}
}
